| Date | Block | dexTVL | bridgeTVL | liquidStakingTVL | crowdloanTVL | LPTokenStakingTVL | stableCoinTVL | OverallTVL |
|---|---|---|---|---|---|---|---|---|
| 2022-06-21 | 2,135,922 | 3,727,008 | 13,675,207 | 9,799,394 | 26,940,660 | 3,653,256 | 11,270,679 | 42,125,544 |
| 2022-06-22 | 2,142,278 | 3,634,993 | 13,363,716 | 9,604,004 | 26,269,636 | 3,555,480 | 10,997,083 | 41,155,276 |
| 2022-06-23 | 2,148,575 | 3,646,287 | 14,090,545 | 9,918,909 | 27,162,162 | 3,593,146 | 11,193,642 | 42,442,529 |
| 2022-06-24 | 2,155,139 | 3,752,477 | 14,986,389 | 10,567,805 | 28,917,147 | 3,721,508 | 11,840,803 | 44,868,982 |
| 2022-06-25 | 2,161,562 | 3,759,708 | 14,905,610 | 10,473,270 | 28,632,500 | 3,750,830 | 11,689,145 | 44,578,563 |
| 2022-06-26 | 2,168,013 | 3,851,061 | 13,132,847 | 9,304,393 | 26,522,711 | 3,769,934 | 11,280,487 | 41,338,722 |
| 2022-06-27 | 2,173,904 | 3,734,609 | 13,187,844 | 9,347,125 | 26,615,421 | 3,767,156 | 11,178,714 | 41,215,449 |
| 2022-06-28 | 2,178,195 | 3,752,755 | 12,722,099 | 9,052,519 | 25,664,763 | 3,730,180 | 10,944,605 | 40,202,157 |
Please click on the Source Code link at the top of the document to see the full source code. Here is a summary:
{
dailyPools {
nodes {
timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}
}
}
}
{
dailyCollaterals {
nodes {
collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount
}
}
}
---
title: "Acala / Karura TVL Dashboard"
output:
flexdashboard::flex_dashboard:
orientation: rows
vertical_layout: scroll
social: menu
source_code: embed
params:
network: Karura
window: 7
---
```{css custom1, echo=FALSE}
.dataTables_scrollBody {
max-height: 100% !important;
}
```
```{r global, include=FALSE}
library(knitr)
knitr::opts_chunk$set(
message = FALSE,
warning = FALSE,
comment = "#>"
)
library(ggplot2)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(formattable)
library(ghql)
x <- GraphqlClient$new()
# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)
window = params$window
# window <- today() - as.Date("2021-12-31") + 1
network = params$network
# Liquidity Pool TVL ($3.72M) = totalDefiTvl = totalDexTvl + totalDexLockedTvl + homaLocked + stableCoinTvl
dex <- getDailyPools_acala_dex(network, window) %>%
setorder(Date, pair)
dailyTVL <- dex[, .(max(updateAtBlock.id), sum(tvlUSD)), by = Date] %>%
setnames(c("V1","V2"), c("Block","dexTVL"))
library(reticulate)
# use_python("/opt/homebrew/bin/python3.9")
```
```{python, include=FALSE}
from substrateinterface import SubstrateInterface
import pandas as pd
def getTotalIssuanceKSM():
url = 'wss://karura.polkawallet.io'
substrate = SubstrateInterface(url)
hash = substrate.get_block_hash(block_id)
timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
# Issuance
ksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)
lksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)
result = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
# homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
try:
homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
amount = 0
for res in homa:
amount += res[1].value['bonded']
# LKSM to KSM ratio
ratio = lksm.value / amount
except:
amount = 0
# LKSM to KSM ratio
ratio = 8.5
# Build price DataFrame
p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
price = p.value['value'] / 10**18
bridge = (float(ksm.value / 10**12) + float(lksm.value / 10**12 / ratio)) * price
liquidKSM = float(amount / 10**12) * price
crowdloanTVL = (501137661910050505 / 10**12) * price
return [bridge, liquidKSM, crowdloanTVL, block_id, ratio]
def getOraclePrices():
if r.network=="Karura":
url = 'wss://karura.polkawallet.io'
else:
url = 'wss://acala-rpc-0.aca-api.network'
substrate = SubstrateInterface(url)
hash = substrate.get_block_hash(block_id)
timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
data = []
for res in p:
outi = {"token": str(res[0].value), "price": res[1].value['value'] / 10**18}
data.append(outi)
out = pd.DataFrame(data)
return out
def getRewards():
if r.network=="Karura":
url = 'wss://karura.polkawallet.io'
else:
url = 'wss://acala-rpc-0.aca-api.network'
substrate = SubstrateInterface(url)
hash = substrate.get_block_hash(block_id)
timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
data = []
rewards = substrate.query_map(module='Rewards',storage_function='PoolInfos', block_hash = hash)
for res in rewards:
if res[0][0] == 'Dex':
pair = res[0].value['Dex']['DexShare']
lp = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'DexShare': pair}], block_hash = hash)
legs = substrate.query(module='Dex',storage_function='LiquidityPool', params = [pair], block_hash = hash)
outi = {"pair0": str(pair[0]), "pair1": str(pair[1]), "lp": str(lp.value), "leg0": str(legs[0].value), "leg1": str(legs[1].value)}
data.append(outi)
out = pd.DataFrame(data)
return out
def getTotalIssuanceDOT():
url = 'wss://acala-rpc-0.aca-api.network'
substrate = SubstrateInterface(url)
hash = substrate.get_block_hash(block_id)
timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
# Total Issuance data
dot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'DOT'}], block_hash = hash)
ldot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LDOT'}], block_hash = hash)
lcdot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)
# homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
try:
homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
amount = 0
for res in homa:
amount += res[1].value['bonded']
# LKSM to KSM ratio
ratio = ldot.value / amount
except:
amount = 0
# LDOT to DOT ratio
ratio = 10
# Build price DataFrame
tmp = []
p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
outi = {"id": {'Token': 'AUSD'}, "price": 1}
tmp.append(outi)
for res in p:
outi = {"id": res[0].value, "price": res[1].value['value'] / 10**18}
tmp.append(outi)
if res[0].value=={'Token': 'DOT'}:
outi = {"id": {'LiquidCrowdloan': 13}, "price": res[1].value['value'] / 10**18 / 1.4}
tmp.append(outi)
outi = {"id": {'Token': 'LDOT'}, "price": res[1].value['value'] / 10**18 / ratio}
tmp.append(outi)
# Price lookup function
p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'DOT'}], block_hash = hash)
price = p.value['value'] / 10**18
# Decimals lookup function
tmp = [{'id': {'Token': 'ACA'}, 'decimals': 12}, {'id': {'Token': 'DOT'}, 'decimals': 10}, {'id': {'Token': 'LDOT'}, 'decimals': 10}, {'id': {'LiquidCrowdloan': 13}, 'decimals': 10}, {'id': {'Token': 'AUSD'}, 'decimals': 12}]
decimals = pd.DataFrame(tmp)
def getDecimals(token):
return int(decimals[decimals['id'] == token]['decimals'])
bridge = (float(dot.value / 10**10) + float(ldot.value / 10**10 / ratio)) * price
liquidDOT = float(amount / 10**10) * price
lcDOT = float(lcdot.value / 10**10) * price
crowdloanTVL = (325159802323576263 / 10**10) * price
return [bridge, liquidDOT, crowdloanTVL, lcDOT, block_id, ratio]
```
```{r tvl, cache = TRUE, include=FALSE}
# dailyTVL[, M := month(Date) %+% year(Date)]
# dailyTVL[, maxDate := max(Date), by = M]
# dailyTVL <- dailyTVL[Date == maxDate]
# LP Token Staking TVL = totalDexTvl = useTotalDexPoolTVL()
# totalDexLockedTvl = useTotalStaking() = api.query.dex?.liquidityPool
# KSM Bridge TVL = totalIssuanceKSM = useTotalIssuance('KSM') = api.query.tokens?.totalIssuance
dailyTVL[, bridgeTVL := 0]
# Liquid KSM TVL = totalLocked in Homa = useState()
dailyTVL[, liquidStakingTVL := 0]
# homeLocked = totalStaking * price
dailyTVL[, crowdloanTVL := 0]
dailyTVL[, LPTokenStakingTVL := 0]
if (tolower(network) == "acala") dailyTVL[, lcdotTVL := 0]
for (i in 1:nrow(dailyTVL)) {
py_run_string("block_id = " %+% dailyTVL$Block[i])
if (tolower(network) == "karura") {
tmp = py$getTotalIssuanceKSM()
} else {
tmp = py$getTotalIssuanceDOT()
dailyTVL$lcdotTVL[i] <- tmp[4]
}
dailyTVL$bridgeTVL[i] <- tmp[1]
dailyTVL$liquidStakingTVL[i] <- tmp[2]
dailyTVL$crowdloanTVL[i] <- tmp[3]
# LPTokenStakingTVL
rewards1 <- py$getRewards()
rewards <- as.data.table(rewards1)
# sort(unique(c(rewards$pair0, rewards$pair1)))
rewards[, pair0 := fixToken(pair0)]
rewards[, pair1 := fixToken(pair1)]
rewards <- merge(rewards, tokens, by.x = 'pair0', by.y="Token")
setnames(rewards, "decimals", "decimals0")
rewards[, Name := NULL]
rewards <- merge(rewards, tokens, by.x = 'pair1', by.y="Token")
setnames(rewards, "decimals", "decimals1")
rewards[, Name := NULL]
rewards[, decimals0 := as.numeric(decimals0)]
rewards[, decimals1 := as.numeric(decimals1)]
rewards[, lpDecimals := 12]
rewards[pair0=='DOT' & pair1=='LCDOT', lpDecimals := 10]
rewards[, lp := as.numeric(lp) / 10**lpDecimals]
rewards[, leg0 := as.numeric(leg0) / 10**decimals0]
rewards[, leg1 := as.numeric(leg1) / 10**decimals1]
price = py$getOraclePrices() %>%
as.data.table
price[, token := fixToken(token)]
rewards <- merge(rewards, price, by.x = "pair0", by.y="token", all.x = TRUE)
setnames(rewards, "price", "price0")
rewards <- merge(rewards, price, by.x = "pair1", by.y="token", all.x = TRUE)
setnames(rewards, "price", "price1")
rewards[pair0 %in% c('KUSD', "AUSD"), price0 := 1]
rewards[pair1 %in% c('KUSD', "AUSD"), price1 := 1]
rewards[, tvl := leg0 * price0 + leg1 * price1]
rewards[is.na(tvl), tvl := (leg0 * price0) * 2]
rewards[is.na(tvl), tvl := (leg1 * price1) * 2]
rewards[, lpPrice := tvl / lp]
dailyTVL$LPTokenStakingTVL[i] <- sum(rewards$tvl, na.rm = TRUE)
}
# stableCoinTvl
# const stableCoinTVL = useTotalLocked(); = api.query.loans?.totalPositions
collateral <- getLoansDailyCollateral_acala_loan(network, window, staging = FALSE) %>%
setorder(Date, collateral.id)
stableCoinTvl <- collateral[, sum(depositVolumeUSD), by = Date] %>%
setnames("V1", "stableCoinTVL")
dailyTVL <- merge(dailyTVL, stableCoinTvl, by = "Date", all.x = TRUE)
# If monthly, remove these fields
try(dailyTVL[, M := NULL])
try(dailyTVL[, maxDate := NULL])
# Make columns numeric
for (col in names(dailyTVL)[-1]) set(dailyTVL, j=col, value=as.numeric(dailyTVL[[col]]))
if (tolower(network) == "acala") {
dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + lcdotTVL + stableCoinTVL]
} else {
dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + stableCoinTVL]
}
# merge history
history <- fread(network %+% "_TVL.csv")
history[, Date := as.Date(Date)]
mindate <- min(dailyTVL$Date)
dailyTVL <- rbind(history[Date < mindate], dailyTVL, use.names = TRUE)
fwrite(dailyTVL, file = network %+% "_TVL.csv")
```
# `r network` {.tabset}
Row
----
### Components of Total Value Locked
```{r k_plot1}
if (tolower(network) == "acala") {
dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, lcdotTVL, stableCoinTVL)],
id.vars = "Date",
value.name = "TVL")
cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'LCDOTTVL', 'stableCoinTVL')
} else {
dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, stableCoinTVL)],
id.vars = "Date",
value.name = "TVL")
cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'stableCoinTVL')
}
dat[, TVL := TVL / 1e6]
# Stacked
ggplot(dat, aes(fill=variable, y=TVL, x=Date)) +
geom_bar(position="stack", stat="identity") +
ggtitle(label="Components of Total Value Locked") +
ylab("Totval Value Locked (in millions USD)")
```
Row
----
### Overall Total Value Locked (TVL)
* *The Overall TVL in the table below does not includes the Crowdloan TVL, which also seems to be the case on the http://stats.`r tolower(network)`.network site.*
```{r k_tvl}
knitr::kable(dailyTVL, escape = FALSE, format.args = list(big.mark = ",")) %>%
kable_styling()
```
Row
----
### Sources and Notes
Please click on the *Source Code* link at the top of the document to see the full source code. Here is a summary:
#### dexTVL
- sum of tvlUSD from the xx function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R. You can also use the [Karura-dex](https://explorer.subquery.network/subquery/AcalaNetwork/karura-dex) project hosted on Subquery Network with this query:
```
{
dailyPools {
nodes {
timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}
}
}
}
```
#### LPTokenStakingTVL
- ACA (or KAR) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Rewards',storage_function='PoolInfos', block_hash = hash)*
- mulitplied by ACA (or KAR) price for the specific block number
#### BridgeTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)*
- LKSM (or LDOT) obtained from function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)*
- both mulitplied by KSM (or DOT) price for the specific block number
#### liquidStakingTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)*
- mulitplied by KSM (or DOT) price for the specific block number
#### lcdotTVL (Acala only)
- LCDOT obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)*
- mulitplied by DOT price for the specific block number
#### stableCoinTVL
- sum of depositVolumeUSD from the *getLoansDailyCollateral_acala_loan* function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R. You can also use the [Karura-loan]https://explorer.subquery.network/subquery/AcalaNetwork/karura-loan) project hosted on Subquery Network with this query:
```
{
dailyCollaterals {
nodes {
collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount
}
}
}
```